const O = require('./O')
// const Db = require('./Db')
// 导入后请重新定义 TABLE
// eq:  Object.assign({},Sql,{ TABLE: env.MYSQL.PREFIX + 'user' })
// 关于 当前读 for update
  // 一锁二判三更新
  // 查询加排他锁 gap间隙锁
  // FOR UPDATE 仅适用于InnoDB，且必须在事务区块(BEGIN/ COMMIT) 中才能生效
  //    (明确指定主键，并且有此数据，row lock也就是行锁)
  //    (明确指定主键，若查无此数据，无lock)
  //    (无主键，table lock)
  //    (主键不明确，table lock)
  // 事务中需对更新的字段进行判断时需要加，
  //      比如余额不足时应该给予提醒，而不是最终执行后出现 负的余额或 SQL错误等错误
const Sql = {
  TABLE: '',
  // last : limit / for update 事务中判断时 / ..
  select(col = '*', where = '', last = '') {
    O.isStringEmp(this.TABLE, false, 'Sql.select: must define TABLE ')
    O.isString(col, '')
    O.isString(where, '')
    O.isString(last, '')
    return `select ${col ? col : '*'} from ${this.TABLE} ${where ? 'where ' + where : ''} ${last}`
  },
  selectForUpd(col = '*', where = '', last = '') {
    return this.select(col, where, `${last} for update`)
  },
  // last : for update 事务中判断时
  selectOne(col = '*', where = '',last='') {
    return this.select(col, where, `limit 1 ${last}`)
  },
  selectOneForUpd(col = '*', where = '') {
    return this.selectOne(col, where, 'for update')
  },
  selectCount(where = '') {
    return this.selectOne('count(1) as c', where)
  },
  // col:  name,nick,pass,...
  //        需要自行准备 预处理的values[]
  insert(col) {
    O.isStringEmp(this.TABLE, false, 'Sql.insert: must define TABLE ')
    O.isString(col,'Sql.inert need string')
    if(!col) throw Error('Sql: insert should not null')
    const value = '?,'.repeat(col.split(',').length).slice(0,-1)
    return `insert into ${this.TABLE} (${col}) values(${value})`
  },
  // obj : object{...}
  // return {sql:'name,...',values:[2,3]}
  insertObj(obj) {
    O.isObject(obj,'Sql.insertObj need object')
    let dataKey   = []
    let dataValue = []
    Object.keys(obj).forEach(k=>{
      if(O.isStringOrNum(obj[k])){
        dataKey.push(k)
        dataValue.push(obj[k])
      }
    })
    return {
      sql: this.insert(dataKey.join(',')),
      value: dataValue
    }
  },
  // TODO
  insertObjs(objArr) {
    O.isStringEmp(this.TABLE, false, 'Sql.insertObjs: must define TABLE ')
    O.isArray(objArr,'Sql.insertObjs need object array')
    throw new Error('TODO')
    // if(!col) throw Error('Sql: insert should not null')
    // const value = '?,'.repeat(col.split(',').length).slice(0,-1)
    // return `insert into ${this.TABLE} (${col}) values(${value})`
  },
  // last : order by .. limit x
  delete(where,last='') {
    O.isStringEmp(this.TABLE, false, 'Sql.delete: must define TABLE ')
    if(!where) throw Error('Sql: should not delete all')
    return `delete from ${this.TABLE} where ${where} ${last}`
  },
  // whereAndObj : obj
  // last : order by .. limit x
  deleteObj(whereAndObj,last='') {
    O.isObject(whereAndObj, 'Sql.insertObj need object')
    let dataKey   = []
    let dataValue = []
    Object.keys(whereAndObj).forEach(k => {
      if (O.isStringOrNum(whereAndObj[k])) {
        dataKey.push(`${k}=?`)
        dataValue.push(whereAndObj[k])
      }
    })
    return {
      sql: this.delete(dataKey.join(' and '),last),
      value: dataValue
    }
  },
  // last:  order by id asc limit x
  update(setCols,where='',last='') {
    O.isStringEmp(this.TABLE, false, 'Sql.update: must define TABLE ')
    // a=2,c=3  ==>  需要的话DB里面处理成{sql:'a=?,c=?',values:[2,3]}
    if(!setCols) throw Error('Sql: should not update null')
    return `update ${this.TABLE} set ${setCols} ${where ? 'where ' + where : ''} ${last}`
  },
  updateObjById(obj){
    const {id} = obj
    delete obj.id
    return this.updateObj(obj, { id },'limit 1')
  },
  updateObj(setObj,whereAndObj,last='') {
    O.isStringEmp(this.TABLE, false, 'Sql.updateObj: must define TABLE ')
    O.isObject(setObj, 'Sql.updateObj: set null not allowed')
    O.isObject(whereAndObj, 'Sql.updateObj: where null allowed')
    // name =?, nick =?,...
    // id =? and nick =? ...
    // values[]
    let dataValue = []
    let setKey   = []
    let whereKey = []
    Object.keys(setObj).forEach(k=>{
      if (O.isStringOrNum(setObj[k])) {
        setKey.push(`${k}=?`)
        dataValue.push(setObj[k])
      }
    })
    Object.keys(whereAndObj).forEach(k=>{
      if (O.isStringOrNum(whereAndObj[k])) {
        whereKey.push(`${k}=?`)
        dataValue.push(whereAndObj[k])
      }
    })
    return { sql: `update ${this.TABLE} set ${setKey.join(',')}  where ${whereKey.join(' and ')} ${last}`,value: dataValue}
  },
  // 覆盖插入
  // replace(col) {
  //  return 'replace into ...'
  // },
  // 大量插入使用 错误不插入
  // ignore(col) {
  //  return 'insert ignore into ...'
  // },
}

// 不要暴露和引用具体方法 防止this意外失效
module.exports = Sql